package lulin.w09.dao;

import lulin.w09.entity.Department;
import lulin.w09.entity.School;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;

import java.sql.PreparedStatement;
import java.util.List;

@Repository
public class DepartmentDao implements DepartmentDaoInterface {

    @Autowired
    private JdbcTemplate jdbcTemplate;

    @Override
    public Long addDepartment(Department department) {
        String sql = "INSERT INTO department (description, no, remarks, school_id) VALUES (?,?,?,?)";
        KeyHolder keyHolder = new GeneratedKeyHolder();
        jdbcTemplate.update(connection -> {
            PreparedStatement ps = connection.prepareStatement(sql, new String[]{"id"});
            ps.setString(1, department.getDescription());
            ps.setString(2, department.getNo());
            ps.setString(3, department.getRemarks());
            ps.setLong(4, department.getSchoolId());
            return ps;
        }, keyHolder);
        return keyHolder.getKey().longValue();
    }

    @Override
    public int deleteDepartment(Long id) {
        return jdbcTemplate.update("DELETE FROM department WHERE id = ?", id);
    }

    @Override
    public int updateDepartment(Department department) {
        return jdbcTemplate.update("UPDATE department SET description = ?, no = ?, remarks = ?, school_id = ? WHERE id = ?",
                department.getDescription(), department.getNo(), department.getRemarks(), department.getSchoolId(), department.getId());
    }

    @Override
    public Department getDepartment(Long id) {
        Department department = jdbcTemplate.queryForObject("SELECT * FROM department WHERE id = ?",
                new BeanPropertyRowMapper<>(Department.class), id);
        if (department != null) {
            School school = jdbcTemplate.queryForObject("SELECT * FROM school WHERE id = ?",
                    new BeanPropertyRowMapper<>(School.class), department.getSchoolId());
            department.setSchool(school);
        }
        return department;
    }

    @Override
    public List<Department> getAllDepartments() {
        List<Department> departments = jdbcTemplate.query("SELECT * FROM department",
                new BeanPropertyRowMapper<>(Department.class));
        for (Department department : departments) {
            School school = jdbcTemplate.queryForObject("SELECT * FROM school WHERE id = ?",
                    new BeanPropertyRowMapper<>(School.class), department.getSchoolId());
            department.setSchool(school);
        }
        return departments;
    }

    @Override
    public List<Department> getDepartmentsBySchoolId(Long schoolId) {
        List<Department> departments = jdbcTemplate.query("SELECT * FROM department WHERE school_id = ?",
                new BeanPropertyRowMapper<>(Department.class), schoolId);
        for (Department department : departments) {
            School school = jdbcTemplate.queryForObject("SELECT * FROM school WHERE id = ?",
                    new BeanPropertyRowMapper<>(School.class), department.getSchoolId());
            department.setSchool(school);
        }
        return departments;
    }
}    